package ae.pcfc.etks.online.itworx.ehs.web.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

import org.jboss.resource.adapter.jdbc.WrappedConnection;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.jdbc.UncategorizedSQLException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;

import ae.pcfc.etks.online.itworx.ehs.web.dao.IMSDAO;
import ae.pcfc.etks.online.itworx.ehs.web.exception.CommunicationException;
import ae.pcfc.etks.online.itworx.ehs.web.exception.IMSException;
import ae.pcfc.etks.online.itworx.ehs.web.model.IMSComment;
import ae.pcfc.etks.online.itworx.ehs.web.util.UtilCommon;

@Repository
public class IMSDAOImpl extends BaseDAOImpl implements IMSDAO{

	private final String COMMENTS_RECORD_ORACLE_STRUCT = "T_COMMENTS_RECORD";
	private final String COMMENTS_RECORD_ORACLE_ARRAY = "TB_COMMENTS_RECORDS";
	
	private Logger log = LoggerFactory.getLogger(IMSDAOImpl.class);
	
	public String createIMPSRecord(String inspectionCode, String category, String buCode, String requestorId, String createdBy) throws IMSException,CommunicationException{
		
		Object status = null;
		try{
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate()).withProcedureName("PKG_ONLINE.CREATE_IMS_RECORD");
		jdbcCall.setAccessCallParameterMetaData(false);
		jdbcCall = jdbcCall.declareParameters(
				new SqlParameter("P_INSPECTION_CODE", Types.VARCHAR),
				new SqlParameter("P_CATEGORY", Types.VARCHAR),
				new SqlParameter("P_BU_CODE", Types.VARCHAR),
				new SqlParameter("P_REQUESTOR_ID", Types.VARCHAR),
				new SqlParameter("P_CREATED_BY", Types.VARCHAR),
				new SqlOutParameter("P_LOG_ID", Types.VARCHAR));
		SqlParameterSource in = new MapSqlParameterSource()
			.addValue("P_INSPECTION_CODE", inspectionCode)
			.addValue("P_CATEGORY", category)
			.addValue("P_BU_CODE", buCode)
			.addValue("P_REQUESTOR_ID", requestorId)
			.addValue("P_CREATED_BY", createdBy);
		Map<String,Object> result = jdbcCall.execute(in);
		status = result.get("P_LOG_ID");
		}
		catch (DataAccessResourceFailureException dalExp) 
		{
			log.error("IMSDAOImpl.createIMPSRecord() - error occurred while creating new request",dalExp);
			CommunicationException imsCommExp=new CommunicationException(dalExp);
			imsCommExp.setErrorCode("error.ims.communication");
			throw imsCommExp ; 
		}
		catch(UncategorizedSQLException uncategorizedExp)
		{
			log.error("IMSDAOImpl.createIMPSRecord() - error occurred while creating new request",uncategorizedExp);
			IMSException imsExp=new IMSException(uncategorizedExp);
			imsExp.setErrorCode("error.ims.unexpected");
			String cause = (uncategorizedExp.getCause() != null) ? uncategorizedExp.getCause().getMessage():uncategorizedExp.getMessage();
			imsExp.setErrorArgs(new Object[]{cause});
			throw imsExp ; 
		}
		catch(Exception e) {
			log.error("IMSDAOImpl.createIMPSRecord() - error occurred while creating new request",e);
			throw new IMSException(e);
		}
		return UtilCommon.toString(status);
	}
	
	public String cancelIMPSRecord(String imsLogId) throws IMSException,CommunicationException{
		Object status = null;
		try{
			SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate()).withProcedureName("PKG_ONLINE.CANCEL_IMS_RECORD");
			jdbcCall.setAccessCallParameterMetaData(false);
			jdbcCall = jdbcCall.declareParameters(
					new SqlParameter("P_LOG_ID", Types.VARCHAR),
					new SqlOutParameter("P_STATUS", Types.VARCHAR));
			SqlParameterSource in = new MapSqlParameterSource()
				.addValue("P_LOG_ID", imsLogId);
			Map<String,Object> result = jdbcCall.execute(in);
			status = result.get("P_STATUS");
		}
		catch (DataAccessResourceFailureException dalExp) 
		{
			log.error("IMSDAOImpl.cancelIMPSRecord() - error occurred while canceling request ["+imsLogId+"]",dalExp);
			CommunicationException imsCommExp=new CommunicationException(dalExp);
			imsCommExp.setErrorCode("error.ims.communication");
			throw imsCommExp ; 
		}
		catch(UncategorizedSQLException uncategorizedExp)
		{
			log.error("IMSDAOImpl.cancelIMPSRecord() - error occurred while canceling request ["+imsLogId+"]",uncategorizedExp);
			IMSException imsExp=new IMSException(uncategorizedExp);
			imsExp.setErrorCode("error.ims.unexpected");
			String cause = (uncategorizedExp.getCause() != null) ? uncategorizedExp.getCause().getMessage():uncategorizedExp.getMessage();
			imsExp.setErrorArgs(new Object[]{cause});
			throw imsExp ; 
		}
		catch(Exception e){
			log.error("IMSDAOImpl.cancelIMPSRecord() - error occurred while cancel ("+imsLogId+") ims request",e);
			throw new IMSException(e);
		}
		return UtilCommon.toString(status);
	}
		
	public String retrieveIMSStatus(String imsLogId) throws IMSException,CommunicationException{
		Object status = null;
		try{
			SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate()).withProcedureName("PKG_ONLINE.GET_IMS_STATUS");
			jdbcCall.setAccessCallParameterMetaData(false);
			jdbcCall = jdbcCall.declareParameters(
					new SqlParameter("P_LOG_ID", Types.VARCHAR),
					new SqlOutParameter("P_STATUS", Types.VARCHAR));
			SqlParameterSource in = new MapSqlParameterSource()
				.addValue("P_LOG_ID", imsLogId);
			
			Map<String,Object> result = jdbcCall.execute(in);
			status = result.get("P_STATUS");
		}
		catch (DataAccessResourceFailureException dalExp) 
		{
			log.error("IMSDAOImpl.retrieveIMSStatus() - error occurred while retriving request ["+imsLogId+"] status",dalExp);
			CommunicationException imsCommExp=new CommunicationException(dalExp);
			imsCommExp.setErrorCode("error.ims.communication");
			throw imsCommExp ; 
		}
		catch(UncategorizedSQLException uncategorizedExp)
		{
			log.error("IMSDAOImpl.retrieveIMSStatus() - error occurred while retriving request ["+imsLogId+"] status",uncategorizedExp);
			IMSException imsExp=new IMSException(uncategorizedExp);
			imsExp.setErrorCode("error.ims.unexpected");
			String cause = (uncategorizedExp.getCause() != null) ? uncategorizedExp.getCause().getMessage():uncategorizedExp.getMessage();
			imsExp.setErrorArgs(new Object[]{cause});
			throw imsExp ; 
		}
		catch(Exception e){
			log.error("IMSDAOImpl.retrieveIMSStatus() - error occurred while retrieve status for ("+imsLogId+") ims request",e);
			throw new IMSException(e);
		}
		return UtilCommon.toString(status);
	}
	
	public List<IMSComment> retrieveIMSComments(String imsLogId) throws IMSException ,CommunicationException{
		List<IMSComment>  comments = null;
		try {
			SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate()).withProcedureName("PKG_ONLINE.GET_IMS_COMMENTS");
			jdbcCall.setAccessCallParameterMetaData(false);
			jdbcCall = jdbcCall.declareParameters(
					new SqlParameter("P_LOG_ID", Types.VARCHAR),
					new SqlOutParameter( "P_COMMENTS_RECORDS", OracleTypes.CURSOR, new RowMapper<IMSComment>(){
						@Override
						public IMSComment mapRow(ResultSet results, int arg1)
								throws SQLException {
							return new IMSComment(
									results.getString("DEPT_ID"), 
									results.getString("EMP_NAME"),
									results.getInt("INSP_NUM"), 
									results.getInt("COMMENTS_SEQ_NUM"), 
									results.getString("COMMENTS_SEVERITY"), 
									results.getString("COMMENTS_DESC"), 
									results.getString("CUSTOMER_FEEDBACK"), 
									results.getDate("COMMENTS_DATE"));
						}
					}));
			
			SqlParameterSource in = new MapSqlParameterSource()
				.addValue("P_LOG_ID", imsLogId);
			
			Map<String,Object> result = jdbcCall.execute(in);
			comments = (List<IMSComment>) result.get("P_COMMENTS_RECORDS");
		}
		catch (DataAccessResourceFailureException dalExp) 
		{
			log.error("IMSDAOImpl.retrieveIMSComments() - data access error occurred while retrieve IMS Comments for request ["+imsLogId+"]",dalExp);
			CommunicationException imsCommExp=new CommunicationException(dalExp);
			imsCommExp.setErrorCode("error.ims.communication");
			throw imsCommExp ; 
		}
		catch(UncategorizedSQLException uncategorizedExp)
		{
			log.error("IMSDAOImpl.retrieveIMSComments() - un-categorized error occurred while retrieve IMS Comments for request ["+imsLogId+"]",uncategorizedExp);
			IMSException imsExp=new IMSException(uncategorizedExp);
			imsExp.setErrorCode("error.ims.unexpected");
			String cause = (uncategorizedExp.getCause() != null) ? uncategorizedExp.getCause().getMessage():uncategorizedExp.getMessage();
			imsExp.setErrorArgs(new Object[]{cause});
			throw imsExp ; 
		}
		catch(Exception e){
			log.error("IMSDAOImpl.retrieveIMSComments() - error occurred while retrieve ims comments for request ["+imsLogId+"]",e);
			throw new IMSException(e);
		}
		return comments;
	}
	 
	public String setIMSCustomerReply(String imsLogId, List<IMSComment> commentsRecords) throws IMSException,CommunicationException {
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate()).withProcedureName("PKG_ONLINE.SET_IMS_CUSTREPLY");
		jdbcCall.setAccessCallParameterMetaData(false);
		jdbcCall = jdbcCall.declareParameters(
				new SqlParameter("P_LOG_ID", Types.VARCHAR),
				new SqlParameter("P_COMMENTS_RECORDS", OracleTypes.ARRAY),
				new SqlOutParameter("P_STATUS", Types.VARCHAR));
		StructDescriptor structDescriptor=null;
		ArrayDescriptor arrayDescriptor=null;
		
		Object[] arrObj =null;
		Object[][] recObj =null;
		Object status = null;
		Connection jbossConn = null;
		Connection conn = null;
		try{
			jbossConn = getJdbcTemplate().getDataSource().getConnection();
			conn = ((WrappedConnection) jbossConn).getUnderlyingConnection(); 
		
			structDescriptor = StructDescriptor.createDescriptor(COMMENTS_RECORD_ORACLE_STRUCT, conn);
			arrayDescriptor = ArrayDescriptor.createDescriptor(COMMENTS_RECORD_ORACLE_ARRAY, conn);
			
			
			arrObj = new Object[commentsRecords.size()];
			recObj = new Object[commentsRecords.size()][8];

			for(int i=0 ; i < commentsRecords.size() ; i++){
				IMSComment comment = commentsRecords.get(i);
				recObj[i][0]=comment.getImsDeptId();
				recObj[i][1]=comment.getImsInspectorName();
				recObj[i][2]=comment.getImsInspectorNumber();
				recObj[i][3]=comment.getImsCommentSeqNo();
				recObj[i][4]=comment.getImsSeverity();
				recObj[i][5]=comment.getCommentDetails();
				recObj[i][6]=comment.getReply();
				recObj[i][7]=new java.sql.Date(comment.getCommentDate().getTime());
				arrObj[i] = new STRUCT(structDescriptor, conn, recObj[i]);
			}
			ARRAY arr = new ARRAY(arrayDescriptor, conn, recObj);
			
			SqlParameterSource in = new MapSqlParameterSource()
				.addValue("P_LOG_ID", imsLogId)
				.addValue("P_COMMENTS_RECORDS", arr);
			
			Map<String,Object> result = jdbcCall.execute(in);
			status = result.get("P_STATUS");
		}
		catch (DataAccessResourceFailureException dalExp) 
		{
			log.error("IMSDAOImpl.setIMSCustomerReply() - data access error occurred while setting customer reply for request ["+imsLogId+"] with comments ["+commentsRecords+"]",dalExp);
			CommunicationException imsCommExp=new CommunicationException(dalExp);
			imsCommExp.setErrorCode("error.ims.communication");
			throw imsCommExp ; 
		}
		catch(UncategorizedSQLException uncategorizedExp)
		{
			log.error("IMSDAOImpl.setIMSCustomerReply() - un-categorized error occurred while setting customer reply for request ["+imsLogId+"] with comments ["+commentsRecords+"]",uncategorizedExp);
			IMSException imsExp=new IMSException(uncategorizedExp);
			imsExp.setErrorCode("error.ims.unexpected");
			String cause = (uncategorizedExp.getCause() != null) ? uncategorizedExp.getCause().getMessage():uncategorizedExp.getMessage();
			imsExp.setErrorArgs(new Object[]{cause});
			throw imsExp ; 
		}
		catch (Exception e) {
			log.error("IMSDAOImpl.setIMSCustomerReply() - error occurred while set ims comments for request ["+imsLogId+"] ims request with comments ["+commentsRecords+"]",e);
			throw new IMSException(e);
		} finally {
			if(jbossConn != null){
				try{
					jbossConn.close();
				}catch(SQLException e){
					log.error("IMSDAOImpl.setIMSCustomerReply() - error occurred while closing jboss connection for request ["+imsLogId+"] ims request with comments ["+commentsRecords+"]",e);
				}
				if(conn != null){
					conn = null;
				}
			}
		}
		return UtilCommon.toString(status);
	}	
	
	
	/*
	private static DriverManagerDataSource dataSource;
	
	public JdbcTemplate getJdbcTemplate(){
		return new JdbcTemplate(dataSource);
	}
	
	public static void main(String[] args) throws IMSException, CommunicationException{
		dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
		//DEV
		dataSource.setUrl("jdbc:oracle:thin:@jmiddb01.dubaiworld.ae:1669:DNCPRD");
		//SIT
		//dataSource.setUrl("jdbc:oracle:thin:@172.30.128.16:1720:TNCPRD");
		dataSource.setUsername("FZEXPRD_RW");
		dataSource.setPassword("FZEXPRD_RW");
		
		IMSDAOImpl dao = new IMSDAOImpl();
		
		String recordId = null;
		String cancelStatus = null;
		
		//createIMSRecord: DONE
		//System.out.println("recordId : " + (recordId = dao.createIMPSRecord("OFC", "NEW", "DMCC", "C-0030-735648", "MOTHILAL.SIMANLAL")));
		//System.out.println("recordId : " + (recordId = dao.createIMPSRecord("OFC", "NEW", "DMCC", "C-0005-736732", "MOTHILAL.SIMANLAL")));
		//System.out.println("recordId : " + (recordId = dao.createIMPSRecord("OFC", "NEW", "JAFZA", "C-X-731820", "ABDUL.MYDEEN")));
		
		//cancelIMPSRecord: DONE 
		//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100099")));
		//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100100")));
		//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100101")));
		//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100102")));
		//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100103")));
		//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100104")));
		//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100105")));
		//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100106")));
		//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100107")));
		//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100108")));
		//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100109")));
		//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100110")));
		//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100111")));
		
		
		//retrieveIMSStatus: DONE
		//System.out.println("recordId : " + dao.retrieveIMSStatus(recordId));
		
		//createIMSRecord: DONE
		//System.out.println("recordId : " + (recordId = dao.createIMPSRecord("OFC", "NEW", "DMCC", "C-0030-735648", "MOTHILAL.SIMANLAL")));
		
		//retrieveIMSStatus: DONE
		//System.out.println("recordId : " + dao.retrieveIMSStatus(recordId));
		
		//retrieveIMSCommentsRecord:
		
		//List<CommentsRecord> records = dao.retrieveIMSComments("100099");
		//for(CommentsRecord rec: records){
		//	System.out.println(rec);
		//}
//		
//		records.get(0).setCustomerFeedback("1 Cust ReplyXXXXXXXXXXXXXXX");
//		records.get(1).setCustomerFeedback("2 Cust ReplyYYYYYYYYYYYYYYY");
//		records.get(2).setCustomerFeedback("3 Cust ReplyUUUUUUUUUUUUUUU");
//		
//		records.get(0).setCommentsDesc("test1");
//		records.get(1).setCommentsDesc("test2");
//		records.get(2).setCommentsDesc("test3");
//		
//		System.out.println(dao.setIMSCustomerReply("100099", records));
//		
//		
//		List<IMSComment> records2 = dao.retrieveIMSComments("100138");
//		for(IMSComment rec: records2){
//			System.out.println(rec);
//		}
		
		//System.out.println("recordId : " + (recordId = dao.createIMPSRecord("OFC", "NEW", "DMCC", "C-0005-736732", "MOTHILAL.SIMANLAL")));
		try{
			//System.out.println("cancelStatus : " + (cancelStatus = dao.cancelIMPSRecord("100140")));
			//System.out.println("Status : " + (dao.retrieveIMSStatus("3213")));
			//System.out.println("recordId : " + (recordId = dao.createIMPSRecord("OFC", "NEW", "JAFZA", "C-X-026202", "itworx.ahmed")));
			List<IMSComment> records2 = dao.retrieveIMSComments("100156");
			for(IMSComment rec: records2){
				System.out.println(rec);
			}

			
		}catch(Exception e){
			e.printStackTrace();
		}	
	}
*/
}
